<?php

namespace app\services\data\stat\ad\normal;

use app\clickhouse\models\Reports;

class StatReport extends StatBaseReportFirstup
{
    public function __construct($params)
    {
        parent::__construct($params);

        $today = strtotime('today');
        if ($this->etime > $today) {
            $this->etime = $today+86400;
        }
    }

    public function getSql()
    {
        $join             = $this->queryJoin('reports');
        $where            = $this->queryWhere(['time', 'game_id', 'channel', 'game_channel_id', 'media_id', 'media_docking_id', 'plan_id', 'source_type'], 'reports');
        $plan_where       = $this->queryWhere(['owner', 'plan_group_id'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');

        if (in_array('stat_hour', $this->groupbys)) {
            $report_id = Reports::REPORT_TYPE_AD_HOUR;
        } else {
            $report_id = Reports::REPORT_TYPE_AD;
        }

        $sql = "
            SELECT sum(show) AS show, sum(click) AS click,
            sum(cost) AS cost1, sum(cost/(10000+fandian)*10000) as cost2
            FROM(
                SELECT show, click, cost, fandian
                FROM reports FINAL
                {$join}
                LEFT JOIN(
                    SELECT daili_id, fandian, start_time, end_time FROM
                    mysql('{$this->managerHost}', 'xipin_manager', 'ad_media_user_daili_sub', 'xipin_manager', '{$this->managerPwd}')
                ) AS ad_media_user_daili_sub
                ON ad_media_user.daili_id = ad_media_user_daili_sub.daili_id
                WHERE {$where}
                AND {$plan_where}
                AND {$media_user_where}
                AND `#report_type` = {$report_id}
                AND reports.`#create_time` >= ad_media_user_daili_sub.start_time AND reports.`#create_time` <= ad_media_user_daili_sub.end_time
            )
        ";

        return $sql;
    }

    protected function queryGroupBy($table_name = 'firstups')
    {
        foreach ($this->groupbys as $name) {
            switch ($name) {
                case 'stat_hour':
                    $selects[] = "{$table_name}.`stat_hour` AS stat_hour";
                    break;
                case 'stat_date':
                    $selects[] = "toDate({$table_name}.`#create_time`, '{$this->timezone}') AS stat_date";
                    break;
                case 'stat_week':
                    $selects[] = "formatDateTime(toDateTime({$table_name}.`#create_time`), '%Y-%V', '{$this->timezone}') AS stat_week";
                    break;
                case 'stat_month':
                    $selects[] = "formatDateTime(toDateTime({$table_name}.`#create_time`), '%Y-%m', '{$this->timezone}') AS stat_month";
                    break;
                case 'media_id':
                case 'plan_id':
                case 'plan_group_id':
                case 'plan_owner_id':
                    $selects[] = "ad_plan.{$name} AS {$name}";
                    break;
                case 'daili_id':
                    $selects[] = "ad_media_user.{$name} AS {$name}";
                    break;
                case 'adgroup_id':
                    $selects[] = "{$table_name}.myad_group_name AS {$name}";
                    break;
                case 'ad_id':
                    $selects[] = "{$table_name}.myad_ad_name AS {$name}";
                    break;
                case 'creative_id':
                    $selects[] = "{$table_name}.myad_creative_name AS {$name}";
                    break;
                default:
                    $selects[] = "{$table_name}.{$name} AS {$name}";
                    break;
            }
        }

        $select_str  = implode(',', $selects);
        $groupby_str = implode(',', $this->groupbys);

        return [$select_str, $groupby_str];
    }

    public function getSqlByGroup()
    {
        $join             = $this->queryJoin('reports');
        $where            = $this->queryWhere(['time', 'game_id', 'channel', 'game_channel_id', 'media_id', 'media_docking_id', 'plan_id', 'source_type'], 'reports');
        $plan_where       = $this->queryWhere(['owner', 'plan_group_id'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');

        if (in_array('stat_hour', $this->groupbys)) {
            $report_id = Reports::REPORT_TYPE_AD_HOUR;
        } else {
            $report_id = Reports::REPORT_TYPE_AD;
        }

        list($select_str, $groupby_str) = $this->queryGroupBy('reports');

        $sql = "
            SELECT {$select_str}, sum(reports.show) AS show, sum(reports.click) AS click,
            sum(reports.cost) AS cost1, sum(reports.cost/(10000+fandian)*10000) as cost2
            FROM(
                SELECT *, fandian
                FROM reports FINAL
                {$join}
                LEFT JOIN(
                    SELECT daili_id, fandian, start_time, end_time FROM
                    mysql('{$this->managerHost}', 'xipin_manager', 'ad_media_user_daili_sub', 'xipin_manager', '{$this->managerPwd}')
                ) AS ad_media_user_daili_sub
                ON ad_media_user.daili_id = ad_media_user_daili_sub.daili_id
                WHERE {$where}
                AND {$plan_where}
                AND {$media_user_where}
                AND `#report_type` = {$report_id}
                AND reports.`#create_time` >= ad_media_user_daili_sub.start_time AND reports.`#create_time` <= ad_media_user_daili_sub.end_time
            )
            GROUP BY {$groupby_str}
        ";

        return $sql;
    }
}
